//
//  LRStatusDAL.swift
//  LRWeibo
//
//  Created by 雷路荣 on 16/1/3.
//  Copyright © 2016年 leilurong. All rights reserved.
//

import UIKit

class LRStatusDAL: NSObject {
    //程序推导后退，清除数据库时间
    private let overTime: NSTimeInterval = 60 // 7 * 24 * 60 * 60
    //单例
    static let sharedInstance = LRStatusDAL()
    
    private override init() {
        super.init()
    }
    //加载微博数据
    func loadStatus(since_id: Int,max_id: Int, finished: (statuses: [[String: AnyObject]]?, error: NSError? ) -> ()) {
        //1 先加载本地存储在数据库中的微博数据
        LRStatusDAL.sharedInstance.loadCacheStatus(since_id, max_id: max_id) { (statuses) -> () in
            if statuses != nil && statuses?.count > 0  {
                print("***********从本地数据库获取到了数据")
                finished(statuses: statuses, error: nil)
                return
            }
            print("$$$$$$$$$$$$$$$从本地数据库没有获取到数据,去网络加载数据")
            // 没有就从网络加载
            LRNetworkTool.sharedNetworkTool.loadStatusData(since_id, max_id: max_id, finished: { (result, error) -> () in
                
                if error != nil {
                    finished(statuses: nil, error: error)
                    return
                }
                // 4. 网络加载数据后保存数据库 *
                // 判断字典是否有statuses,将statuses的值转成数组,数组里面存放的是字典
                if let array = result?["statuses"] as? [[String: AnyObject]] {
                    self.saveStatus(array)
                    // 5. 将网络返回的数据返回给调用者
                    finished(statuses: array, error: nil)
                }else{
                
                     finished(statuses: nil, error: nil)
                }
                
            })
            
        }
        
    }
    //加载本地微博数据
    private func loadCacheStatus(since_id: Int,max_id: Int, finished: (statuses: [[String: AnyObject]]?) -> ()) {
    
        let userId = LRUserAccount.loadUserAccount()?.uid
        assert(userId != nil,"用户id不能为空" )
        // 1. 编写sql语句
        var sql = "SELECT statusId, status, userId FROM T_Status \n" +
        "WHERE userId = \(userId!) \n"
        // 下拉刷新
        if since_id > 0 {
            sql += "AND statusId > \(since_id) \n"
        } else if max_id > 0 {  // 上拉加载更多
            sql += "AND statusId < \(max_id) \n"
        }
        // 拼接 根据微博id排序,并去最前面20条
        sql += "ORDER BY statusId DESC LIMIT 20;"
        
        print("查询微博数据的sql: \(sql)")
        // 确定sql语句没有错,往下面操作
        LRSQLiteManager.sharedManager.dbQueue.inDatabase { (db) -> Void in
            
            if let recordSet = db.executeQuery(sql) {
                //创建空数组
                var statusesArr = [[String: AnyObject]]()
                //遍历下一个
                while recordSet.next() {
                    //获取微博内容
                    let string = recordSet.stringForColumn("status")
                    //转为二进制
                    let data = string.dataUsingEncoding(NSUTF8StringEncoding)!
                    //解析为字典
                    let dict = try! NSJSONSerialization.JSONObjectWithData(data, options: NSJSONReadingOptions(rawValue: 0)) as! [String: AnyObject]
                    //提交到数组
                    statusesArr.append(dict)
                }
                //完成回调
                finished(statuses: statusesArr)
            }else{
                print("没有获得数据")
                //失败回调
                finished(statuses: nil)
                return
            }
        }
    }
    
    ///将微博数据保存到数据库  statuses: 要保存的微博数据数组
    private func saveStatus(statuses: [[String: AnyObject]]) {
        // 1.编写sql语句
        // OR REPLACE 作用于主键相同的时候
        // 1.添加数据中一定要有主键
        // 2.主键不能是自动增长的
        let sql = "INSERT OR REPLACE INTO \(T_Status) (statusId, status, userId) VALUES (?, ?, ?);"
        // 获取用户的id
        let userId = LRUserAccount.loadUserAccount()?.uid
        
        assert(userId != nil,"用户id不能为空" )
        
        LRSQLiteManager.sharedManager.dbQueue.inTransaction { (db, rollback) -> Void in
            
            for dict in statuses {
                let id = dict["id"] as! Int
                //sqlite不支持字典
                let data = try! NSJSONSerialization.dataWithJSONObject(dict, options: NSJSONWritingOptions(rawValue: 0))
                let string = String(data: data, encoding: NSUTF8StringEncoding)!
                
                if !db.executeUpdate(sql, id, string, userId!) {
                    print("添加数据出错，回滚事务")
                    rollback.memory = true
                    break
                }
            }
            print("添加 \(statuses.count) 条数据到数据库")
        }
    }
    
    ///清除数据库中过时的微博数据
    func clearCachedStatus() {
        // 1. 计算过期时间
        let overDate = NSDate(timeIntervalSinceNow: -overTime)
        // 数据库保存的时间格式 2016-01-03 16:20:56
        print("过期时间:\(overDate)")
        
        let formatter = NSDateFormatter()
        formatter.dateFormat = "yyyy-MM-dd HH:mm:ss"
        let dateString = formatter.stringFromDate(overDate)
        print("dateString:\(dateString)")
        
        // 编写sql语句来清除数据库中的过期微博数据
        let sql = "DELETE FROM T_Status WHERE createTime < '\(dateString)';"
        LRSQLiteManager.sharedManager.dbQueue.inDatabase { (db) -> Void in
            if db.executeUpdate(sql) {
                print("清除过时的微博数据成功")
            }else{
                print("清除过时的微博数据失败")
            }
        }
    }
}































